﻿--|--------------------------------------------------------------------------------
--| [GetCarList] - Insert Procedure Script for Game
--|--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id (N'[dbo].[GetCarList]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) 
  DROP PROCEDURE [dbo].[GetCarList]
GO

CREATE PROCEDURE [dbo].[GetCarList]
(
	@TeamID int
)
AS
	SET NOCOUNT ON

SELECT E.ID, 
	E.Name, 
	E.Description, 
	E.TeamID, 
	E.EntityTypeID, 
	ET.Name as EntityType,
	E.MaxSendingPause,
	EA1.Value as Model,
	EA2.Value as RegistrationNumber,
	EA3.Value as Color
	
FROM dbo.Entity E
	INNER JOIN EntityType ET ON ET.ID = E.EntityTypeID
	LEFT JOIN dbo.EntityAttribute EA1 ON E.ID = EA1.EntityID AND EA1.EntityAttributeTypeID = 'MODL'
	LEFT JOIN dbo.EntityAttribute EA2 ON E.ID = EA2.EntityID AND EA2.EntityAttributeTypeID = 'SRN'
	LEFT JOIN dbo.EntityAttribute EA3 ON E.ID = EA3.EntityID AND EA3.EntityAttributeTypeID = 'CLR'
WHERE E.EntityTypeID = 2 -- Человек
AND		E.TeamID = IsNull(@TeamID,E.TeamID)
	
GO
 


 